import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Random;

/**
 * A tool to fill the database Change_Request table with random change request.
 * 
 */
public class PopulateChangeRequest
{
	Connection conn;
	ArrayList<Integer> questionList = new ArrayList<Integer>();
	ArrayList<RevisionType> crt = new ArrayList<RevisionType>();
	Random rnd = new Random();

	public static void main(String[] args)
	{
		PopulateChangeRequest pop = new PopulateChangeRequest();
		try
		{
			pop.connect();
		} catch (SQLException sqlError)
		{
			sqlError.printStackTrace();
		}

		pop.setup();
		pop.generate();

		try
		{
			pop.close();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
	}

	public PopulateChangeRequest()
	{

	}

	public PopulateChangeRequest(Connection c)
	{
		conn = c;
	}

	/**
	 * Setup a connection to oracle database
	 * 
	 * @throws SQLException
	 *             All database exception are not handled
	 */
	public void connect() throws SQLException
	{
		DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
		conn = DriverManager.getConnection(
				"jdbc:oracle:thin:@146.187.134.17:1542:oracle10g", "cscd494",
				"R3tral435");

	}

	/**
	 * Run some setup stuff. Get list of question, and list of request code
	 */
	public void setup()
	{
		try
		{
			PreparedStatement pstmt = conn
					.prepareStatement("SELECT question_id FROM QUESTION");

			ResultSet rset = pstmt.executeQuery();

			while (rset.next())
			{
				questionList.add(rset.getInt("question_id"));
			}

			rset.close();

			pstmt = conn
					.prepareStatement("SELECT request_code, type_description FROM REVISION_TYPE");

			ResultSet rset1 = pstmt.executeQuery();

			while (rset1.next())
			{
				crt.add(new RevisionType(rset1.getInt("request_code"), rset1
						.getString("type_description")));
			}

			rset1.close();
			pstmt.close();
		} catch (SQLException sqlError)
		{
			sqlError.printStackTrace();
		}
	}

	/**
	 * Close connection to database
	 * 
	 * @throws SQLException
	 *             All database exception is not handled
	 */
	public void close() throws SQLException
	{
		conn.close();
	}

	/**
	 * Fill database with random request. A Question will have 1 in 10 chance of
	 * having a random change request.
	 */
	public void generate()
	{
		int changeProb = 0;
		int request = 0;
		try
		{
			PreparedStatement pstmt = conn
					.prepareStatement("INSERT INTO CHANGE_REQUEST (question_id, q_version,request_code,comments) VALUES(?,?,?,?)");

			for (int i = 0; i < questionList.size(); i++)
			{
				changeProb = rnd.nextInt(10);
				request = rnd.nextInt(crt.size());
				if (changeProb == 1)
				{
					pstmt.setInt(1, questionList.get(i));
					pstmt.setInt(2, 0);
					pstmt.setInt(3, ((RevisionType) crt.get(request)).code);
					pstmt.setString(4,
							((RevisionType) crt.get(request)).description);
					pstmt.execute();
				}
			}
		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 * A storage class to hold the request code with its description
	 * 
	 */
	private class RevisionType
	{
		int code;
		String description;

		/**
		 * Create a new request type
		 * 
		 * @param code
		 *            The request code
		 * @param descript
		 *            The request description
		 */
		public RevisionType(int code, String descript)
		{
			this.code = code;
			this.description = descript;
		}
	}
}
